import Excel from 'exceljs';
import fs from 'fs';

async function createExcelFile() {
    // 创建一个新的工作簿
    let workbook = new Excel.Workbook();

    // 添加一个工作表
    let sheet = workbook.addWorksheet('迭代');

    // 添加表头
    sheet.columns = [
        { header: '需求ID', key: 'backlogId', width: 15 },
        { header: '需求名称', key: 'backlogName', width: 32 },
        { header: '项目名称', key: 'projectName', width: 15 },
        { header: '需求状态', key: 'backlogStatus', width: 15 },
        { header: '需求人员', key: 'backlogPersonName', width: 15 },
        { header: '创建时间', key: 'createTime', width: 15 },
        { header: '执行者姓名', key: 'executorName', width: 15 },
        { header: '开发人员', key: 'developerName', width: 15 },
        { header: '研发工时', key: 'devChargeTime', width: 15 },
    ];

    try {
        const jsonString = fs.readFileSync('src/project_item.json', 'utf8');
        const res = JSON.parse(jsonString);
        if (res.code === 0) {
            const list = res.data.data;
            for (let j = 0; j < list.length; j++) {
                let item = list[j];
                // 添加数据行
                sheet.addRow({
                    backlogId: item.backlogId,
                    backlogName: item.backlogName,
                    projectName: item.projectName,
                    executorName: item.executorName,
                    backlogStatus: item.backlogStatus,
                    backlogPersonName: item.backlogPersonName,
                    createTime: item.createTime,
                    path: item.path,
                    devChargeTime: item.devChargeTime,
                    developerName: item.developerName,
                });
            }
        }
    } catch (e) {
        console.error(e);
    }
    // 写入文件
    await workbook.xlsx.writeFile('dist/迭代SCM3.2.8.xlsx');
}

function readExcel(fileName) {
    let workbook = new Excel.Workbook();

    workbook.xlsx.readFile(fileName).then(function () {
        let worksheet = workbook.getWorksheet(1); //获取第一个worksheet

        let str = '';
        worksheet.eachRow(function (row, rowNumber) {
            // console.log(`行号：${rowNumber}`);
            let rowSize = row.cellCount;
            let numValues = row.actualCellCount;
            if (rowNumber > 1) {
                row.eachCell(function (cell, colNumber) {
                    if (cell.type === Excel.ValueType.Formula) {
                        console.log(`第${rowNumber}行${colNumber}列结果：${cell.result}`);
                    } else {
                        if (colNumber === 1) {
                            str = str + `update mcms_goods_info set goods_reg_cert = null, last_modified = now(), version=version+1 where id = '${cell.value}';\n`;
                        }
                        // console.log(`第${rowNumber}行${colNumber}列类型：${cell.type}, 内容：${cell.value}`);
                    }
                });
            }
            // console.log(`单元格数量: ${rowSize}, 实际数量: ${numValues}`);
        });
        fs.writeFile('dist/output.sql', str, (err) => {
            if (err) throw err;
            console.log('文件已被保存');
        });
    });
}

// 调用函数
createExcelFile();
// readExcel('dist/excel_error.xlsx');